VB Sample Code for modifying database table settings

Private Sub mnuTableTableSettings_Click()
Dim result%, jobnum%, mainjob%, tableCount%, TableN%, logOnPropogate%, sessionPropogate%
Dim TableType As PETableType, LogOnInfo As PELogOnInfo, tableLocation As PETableLocation, SessionInfo As PESessionInfo
Dim testtableType As PETableType, testlogOnInfo As PELogOnInfo, testtableLocation As PETableLocation, testsessionInfo As PESessionInfo

result% = PEOpenEngine()
If result% = 0 Then
MsgBox "Could not start the report engine. Execution must halt.", vbOKOnly + vbCritical, "Serious Error"
End
End If

jobnum% = PEOpenPrintJob(lblReportName.Caption) ' Name from label on sample form
ErrorTrap "OpenPrintJob in TableTableSettings", jobnum%

' Subreport check - if a subreport is currently selected on the main form, jobnum% becomes the subreport
If lblSubreportName.Visible Then
mainjob% = jobnum%
jobnum% = PEOpenSubreport(mainjob%, lblSubreportName.Caption)
ErrorTrap "OpenSubReport in TableTableSettings", mainjob%
End If

' Define the size of the table-related structures
TableType.StructSize = PE_SIZEOF_TABLE_TYPE
LogOnInfo.StructSize = PE_SIZEOF_LOGON_INFO
tableLocation.StructSize = PE_SIZEOF_TABLE_LOCATION
SessionInfo.StructSize = PE_SIZEOF_SESSION_INFO
testtableType.StructSize = PE_SIZEOF_TABLE_TYPE
testlogOnInfo.StructSize = PE_SIZEOF_LOGON_INFO
testtableLocation.StructSize = PE_SIZEOF_TABLE_LOCATION
testsessionInfo.StructSize = PE_SIZEOF_SESSION_INFO

' Load the Sections form for re-use as a available tables form
Load Sections
CenterForm Sample, Sections
Sections.Caption = "Tables"

' Get number of tables used in report
tableCount% = PEGetNTables(jobnum%)
ErrorTrap "GetNTables in TableTableSettings", jobnum%

' Fill section list on section form with tables
' It would be nice to use the names of the tables, but there's no way to find out what they are
For TableN% = 0 To tableCount% - 1
Sections!lstSections.AddItem "Table " & TableN%
Next TableN%

' Start a loop for setting different table settings - exit by pressing ok on the sections form
Do While True
' Show Section selector form 1 ' Modally
Sections.Show 1 ' Modal
' Find which button was pressed to hide the Sections form
Select Case Sections.Tag
Case "Ok"
Exit Do
Case "Edit"

' Get table number from table list on sections form
TableN% = Sections!lstSections.ListIndex

' Load TableSettings form to fill in table settings data
Load TableSettings
CenterForm Sample, TableSettings

' Load table type data
result% = PEGetNthTableType(jobnum%, TableN%, TableType)
ErrorTrap "GetNthTableType in TableTableSettings", jobnum%
TableSettings!lblDLLName.Caption = TableType.DLLName
TableSettings!lblDescriptiveName.Caption = TableType.DescriptiveName
Select Case TableType.DBType
Case PE_DT_STANDARD
TableSettings!lblDBType.Caption = "Standard"
Case PE_DT_SQL
TableSettings!lblDBType.Caption = "SQL"
End Select

' Load logoninfo data
result% = PEGetNthTableLogOnInfo(jobnum%, 0, logOnInfo.ServerName, logOnInfo.DatabaseName, logOnInfo.UserID)
ErrorTrap "GetNthTableLogOnInfo in TableTableSettings", jobnum%
TableSettings!txtServerName.Text = LogOnInfo.ServerName
TableSettings!txtDatabaseName.Text = LogOnInfo.DatabaseName
TableSettings!txtLogOnUserID.Text = LogOnInfo.UserID
' Load location data
result% = PEGetNthTableLocation(jobnum%, TableN%, tableLocation.Location)
ErrorTrap "GetNThTableLocation in TableTableSettings", jobnum%
TableSettings!txtTableLocation.Text = tableLocation.Location

' Load session info data
result% = PEGetNthTableSessionInfo(jobnum%, tableN%, sessionInfo.UserID, sessionInfo.SessionHandle)
ErrorTrap "GetNthTableSessionInfo in TableTableSettings", jobnum%
TableSettings!txtSessionUserID.Text = SessionInfo.UserID
TableSettings!txtSessionHandle.Text = SessionInfo.SessionHandle

' Another infinite loop
Do While True
' Display TableSettings form
TableSettings.Show 1 ' Modal
Select Case TableSettings.Tag
Case "Ok"
' Get propagation flags
If MsgBox("Propagate changes to log on info across tables?", vbYesNo + vbQuestion, "Propagate Log On Changes?") = vbYes Then logOnPropogate% = True Else logOnPropogate% = False
If MsgBox("Propagate changes to session info across tables?", vbYesNo + vbQuestion, "Propagate Session Changes?") = vbYes Then sessionPropogate% = True Else sessionPropogate% = False

' Set log on info
LogOnInfo.ServerName = TableSettings!txtServerName.Text & Chr$(0)
LogOnInfo.DatabaseName = TableSettings!txtDatabaseName.Text & Chr$(0)
LogOnInfo.UserID = TableSettings!txtLogOnUserID.Text & Chr$(0)
LogOnInfo.Password = TableSettings!txtLogOnPassword.Text & Chr$(0)
result% = PESetNthTableLogOnInfo(jobnum%, tableN%, logOnInfo.ServerName, logOnInfo.DatabaseName, logOnInfo.UserID, logOnInfo.Password, logOnPropogate%)
ErrorTrap "SetNthTableLogOnInfo in TableTableSettings", jobnum%

' Set table location
tableLocation.Location = TableSettings!txtTableLocation.Text & Chr$(0)
result% = PESetNthTableLocation(jobnum%, TableN%, tableLocation.Location)
ErrorTrap "SetNthTableLocation in TableTableSettings", jobnum%

' Set table session info
SessionInfo.UserID = TableSettings!txtSessionUserID.Text & Chr$(0)
SessionInfo.Password = TableSettings!txtSessionPassword.Text & Chr$(0)
SessionInfo.SessionHandle = Val(TableSettings!txtSessionHandle.Text)
result% = PESetNthTableSessionInfo(jobnum%, tableN%, sessionInfo.UserID, sessionInfo.Password, SessionInfo.SessionHandle, sessionPropogate%)
ErrorTrap "SetNthTableSessionInfo in TableTableSettings", jobnum%

Exit Do

Case "Test"
' To test table settings properly, we must set all the revised settings
' on the table settings form into the print job, but after testing, must
' also set the settings back to the original ones - changes are only set
' when the user presses "Ok"
' So with all original settings loaded in the various structures, a "test"
' set of structures pick up the new settings from the form and load them
' into the print job.
' Then the table connectivity is tested, the results reported and the
' table settings restored from the original set of structures

' Set log on info into temp structures
testlogOnInfo.ServerName = TableSettings!txtServerName.Text & Chr$(0)
testlogOnInfo.DatabaseName = TableSettings!txtDatabaseName.Text & Chr$(0)
testlogOnInfo.UserID = TableSettings!txtLogOnUserID.Text & Chr$(0)
testlogOnInfo.Password = TableSettings!txtLogOnPassword.Text & Chr$(0)
result% = PESetNthTableLogOnInfo(jobnum%, tableN%, testlogOnInfo.ServerName, testlogOnInfo.DatabaseName, testlogOnInfo.UserID, testlogOnInfo.Password, False)
ErrorTrap "SetNthTableLogOnInfo for Test in TableTableSettings", jobnum%

' Set table location into temp structures
testtableLocation.Location = TableSettings!txtTableLocation.Text & Chr$(0)
result% = crPESetNthTableLocation(jobnum%, TableN%, testtableLocation.Location)
ErrorTrap "SetNthTableLocation for Test in TableTableSettings", jobnum%

' Set table session info into temp structures
testsessionInfo.UserID = TableSettings!txtSessionUserID.Text & Chr$(0)
testsessionInfo.Password = TableSettings!txtSessionPassword.Text & Chr$(0)
testsessionInfo.SessionHandle = Val(TableSettings!txtSessionHandle.Text)
result% = PESetNthTableSessionInfo(jobnum%, tableN%, sessionInfo.UserID, sessionInfo.Password, SessionInfo.SessionHandle, sessionPropogate%)
ErrorTrap "SetNthTableSessionInfo for Test in TableTableSettings", jobnum%

' Test connectivity
result% = PETestNthTableConnectivity(jobnum%, TableN%)
' If there's an error, result% will equal zero, ErrorTrap will present the message
If result% = 0 Then
ErrorTrap "TestNthTableConnectivity in TableTableSettings", jobnum%
Else
MsgBox "Table Connectivity tests out fine", vbOKOnly, "Passed Test"
End If

' Restore settings
result% = PESetNthTableLogOnInfo(jobnum%, tableN%, logOnInfo.ServerName, logOnInfo.DatabaseName, logOnInfo.UserID, logOnInfo.Password, logOnPropogate%)
ErrorTrap "SetNthTableLogOnInfo for UnTest in TableTableSettings", jobnum%
result% = PESetNthTableLocation(jobnum%, TableN%, tableLocation.Location)
ErrorTrap "SetNthTableLocation for UnTest in TableTableSettings", jobnum%
result% = PESetNthTableSessionInfo(jobnum%, tableN%, sessionInfo.UserID, sessionInfo.Password, SessionInfo.SessionHandle, sessionPropogate%)
ErrorTrap "SetNthTableSessionInfo for UnTest in TableTableSettings", jobnum%

Case "Cancel"
MsgBox "Cancel was pressed - no changes will be made to the table settings", vbOKOnly + vbCritical, "Cancel Pressed"
Exit Do
End Select
Loop

Unload TableSettings
End Select
Loop
Unload Sections

' Offer opportunity to see what you did to the report
If MsgBox("Do you want to preview the report?", vbYesNo + vbQuestion, "Preview Report?") = vbYes Then
' Simplified version of the custom-link preview routine (no custom buttons)
result% = PEOutputToWindow(jobnum%, "Table Settings Demonstration Preview" & Chr$(0), CW_USEDEFAULT, CW_USEDEFAULT, CW_USEDEFAULT, CW_USEDEFAULT, 0, 0)
ErrorTrap "OutputtoWindow in TableTableSettings", jobnum%

result% = PEStartPrintJob(jobnum%, True)
ErrorTrap "StartPrintJob in TableTableSettings", jobnum%

result% = 1
Do While result% <> 0
DoEvents
DoEvents
result% = PEGetWindowHandle(jobnum%)
Loop
End If

' Close print job and engine
' Subreport check - if a subreport is currently open, call PreviewReport to offer a chance to
' preview the main report, then close the subreport and main report
If lblSubreportName.Visible Then
PreviewReport mainjob%
result% = PECloseSubreport(jobnum%)
ErrorTrap "CloseSubReport in TableTableSettings", mainjob%
PEClosePrintJob mainjob%
Else
PEClosePrintJob jobnum%
End If

PECloseEngine

MsgBox "Table Settings Complete!", vbOKOnly, "Operation Succeeded"

End Sub
ActiveX
Private Sub mnuTableTableSettings_Click()
Dim TempText As String
Dim hwndPreviewWindow As Long

CrystalReport1.ReportFileName = lblReportName.Caption ' Name from label on sample form

' Load TableSettings form to fill in table settings data
Load TableSettings
CenterForm Sample, TableSettings

TableSettings.Show 1
Select Case TableSettings.Tag
Case "Ok"
' Set log on info
TempText = "DSN = " & TableSettings!txtServerName.Text & ";"
TempText = TempText & "UID = " & TableSettings!txtLogOnUserID.Text & ";"
TempText = TempText & "PWD = " & TableSettings!txtLogOnPassword.Text & ";"
TempText = TempText & "DSQ = " & TableSettings!txtDatabaseName.Text
CrystalReport1.Connect = TempText

' Set table session info
CrystalReport1.UserName = TableSettings!txtSessionUserID.Text
CrystalReport1.Password = TableSettings!txtSessionPassword.Text
CrystalReport1.SessionHandle = Val(TableSettings!txtSessionHandle.Text)

Case "Cancel"
MsgBox "Cancel was pressed - no changes will be made to the table settings", vbOKOnly + vbCritical, "Cancel Pressed"
End Select

Unload TableSettings

' Offer opportunity to see what you did to the report
If MsgBox("Do you want to preview the report?", vbYesNo + vbQuestion, "Preview Report?") = vbYes Then
CrystalReport1.Destination = 0 ' Window
CrystalReport1.Action = 1 ' Print
ErrorTrap "TableSettings"
hwndPreviewWindow = GetActiveWindow()
Do While IsWindow(hwndPreviewWindow)
DoEvents
Loop
End If

' Close the report
CrystalReport1.ReportFileName = ""

MsgBox "Table Settings Complete!", vbOKOnly, "Operation Completed"

End Sub


Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com